package com.sline.day01.smis.dao.impl;

import com.sline.day01.smis.dao.IStudentDao;
import com.sline.day01.smis.domain.Student;

import java.sql.*;
import java.util.ArrayList;

public class StudentDaoImpl implements IStudentDao {
    @Override
    public void save(Student student) {
        // insert into t_student(name, age) values('xx', age);
        StringBuilder sql = new StringBuilder();
        sql.append("insert into t_student(name, age) values('");
        sql.append(student.getName()).append("', ");
        sql.append(student.getAge()).append(");");

        Connection connection = null;
        Statement statement = null;

        try {
            // 加连语执释
            Class.forName("com.mysql.jdbc.Driver");

            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "admin");

            statement = connection.createStatement();

            statement.executeUpdate(sql.toString());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (connection != null) {
                        connection.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public void delete(Long id) {
        String sql = "delete from t_student where id = " + id + ";";

        Connection connection = null;
        Statement statement = null;
        try {
            // 加连语执释
            Class.forName("com.mysql.jdbc.Driver");

            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "admin");

            statement = connection.createStatement();

            statement.executeUpdate(sql);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (connection != null) {
                        connection.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public void update(Long id, Student student) {
        // update t_student name = 'name', age = age where id = id;
        StringBuilder sql = new StringBuilder();
        sql.append("update t_student set name = '");
        sql.append(student.getName()).append("', age = ");
        sql.append(student.getAge()).append(" where id = ");
        sql.append(id).append(";");

        // 加连语执释
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");

            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "admin");

            statement = connection.createStatement();

            statement.executeUpdate(sql.toString());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (connection != null) {
                        connection.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public Student get(Long id) {

        String sql = "select * from t_student where id = " + id + ";";

        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            // 加连语执释
            Class.forName("com.mysql.jdbc.Driver");

            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "admin");

            statement = connection.createStatement();

            rs = statement.executeQuery(sql);
            if (rs.next()) {
                Student st = new Student();

                st.setSid(rs.getLong("id"));
                st.setName(rs.getString("name"));
                st.setAge(rs.getInt("age"));

                return st;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (statement != null) {
                        statement.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    try {
                        if (connection != null) {
                            connection.close();
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        }

        return null;
    }

    @Override
    public ArrayList<Student> list() {
        String sql = "select * from t_student";
        ArrayList<Student> arrayList = new ArrayList<>();

        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        try{
            // 加连语执释
            Class.forName("com.mysql.jdbc.Driver");

            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "admin");

            statement = connection.createStatement();

            rs = statement.executeQuery(sql);
            while (rs.next()) {
                Student st = new Student();

                st.setSid(rs.getLong("id"));
                st.setName(rs.getString("name"));
                st.setAge(rs.getInt("age"));

                arrayList.add(st);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try{
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try{
                    if (statement != null) {
                        statement.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    try{
                        if (connection != null) {
                            connection.close();
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        }

        return arrayList;
    }
}
